# app.py - Flask主服务
from flask import Flask, render_template, jsonify, request
import serial
import sqlite3
import time
import threading
import numpy as np
from datetime import datetime, timedelta
import matplotlib.pyplot as plt
import io
import base64

app = Flask(__name__)

# 初始化串口连接
ser = None

# 尝试连接到Arduino
for port in ['COM3', 'COM4', 'COM5', 'COM6', '/dev/ttyUSB0', '/dev/ttyACM0']:
    try:
        ser = serial.Serial(port, 9600, timeout=1)
        print(f"成功连接到Arduino: {port}")
        break
    except serial.SerialException:
        continue

# 如果没有找到Arduino，则使用模拟模式
if ser is None:
    print("未找到Arduino设备，使用模拟模式")
    simulation_mode = True
else:
    simulation_mode = False

# 模拟模式下的标签数据
simulated_tags = [
    {'tag_id': '11223344', 'action': 'IN'},
    {'tag_id': '22334455', 'action': 'OUT'},
    {'tag_id': '33445566', 'action': 'IN'},
    {'tag_id': '44556677', 'action': 'OUT'},
    {'tag_id': '55667788', 'action': 'IN'}
]

# 模拟模式下的模式切换数据
simulated_mode_switches = ['IN_MODE', 'OUT_MODE', 'IN_MODE', 'OUT_MODE']

# 数据库配置
def init_db():
    conn = sqlite3.connect('warehouse_management.db')
    cursor = conn.cursor()
    
    # 创建物品表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        tag_id TEXT UNIQUE,
        name TEXT NOT NULL,
        description TEXT,
        category TEXT,
        quantity INTEGER DEFAULT 1,
        location TEXT,
        unit_price REAL,
        created_at TEXT,
        updated_at TEXT
    )
    ''')
    
    # 创建出入库记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS inventory_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        tag_id TEXT,
        item_name TEXT,
        action TEXT,
        quantity INTEGER,
        location TEXT,
        reader_id INTEGER,
        timestamp TEXT
    )
    ''')
    
    # 创建位置表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS locations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE,
        description TEXT,
        capacity INTEGER,
        current_count INTEGER DEFAULT 0
    )
    ''')
    
    # 创建默认位置
    cursor.execute("SELECT COUNT(*) FROM locations")
    count = cursor.fetchone()[0]
    if count == 0:
        locations = [
            (1, "货架A1", "第一层货架A区", 50, 0),
            (2, "货架A2", "第二层货架A区", 50, 0),
            (3, "货架B1", "第一层货架B区", 50, 0),
            (4, "货架B2", "第二层货架B区", 50, 0)
        ]
        cursor.executemany(
            "INSERT INTO locations (id, name, description, capacity, current_count) VALUES (?, ?, ?, ?, ?)",
            locations
        )
    
    conn.commit()
    conn.close()

# 初始化数据库
init_db()

# AI库存预测模块
class InventoryPredictor:
    def __init__(self):
        self.reorder_threshold = 10  # 补货阈值
        
    def analyze_consumption(self, item_id, days=30):
        # 分析物品的消费趋势
        end_date = datetime.now()
        start_date = end_date - timedelta(days=days)
        
        conn = sqlite3.connect('warehouse_management.db')
        cursor = conn.cursor()
        
        # 获取物品信息
        cursor.execute("SELECT tag_id, name, quantity FROM items WHERE id = ?", (item_id,))
        item_info = cursor.fetchone()
        
        if not item_info:
            conn.close()
            return None
        
        tag_id = item_info[0]
        
        # 获取指定时间范围内的出库记录
        cursor.execute(
            "SELECT timestamp, quantity FROM inventory_logs WHERE tag_id = ? AND action = 'OUT' AND timestamp >= ? ORDER BY timestamp",
            (tag_id, start_date.strftime('%Y-%m-%d %H:%M:%S'))
        )
        logs = cursor.fetchall()
        conn.close()
        
        if not logs:
            return None
        
        # 计算平均每日消耗量
        total_out = sum([log[1] for log in logs])
        avg_daily_consumption = total_out / days
        
        # 当前库存
        current_stock = item_info[2]
        
        # 计算预计库存耗尽天数
        if avg_daily_consumption > 0:
            days_left = current_stock / avg_daily_consumption
        else:
            days_left = float('inf')
        
        # 生成补货建议
        reorder_suggestion = 0
        if days_left < 7:  # 如果预计7天内耗尽库存
            # 建议补货量 = 平均每日消耗量 * 30天
            reorder_suggestion = int(avg_daily_consumption * 30)
        
        return {
            'item_name': item_info[1],
            'current_stock': current_stock,
            'avg_daily_consumption': round(avg_daily_consumption, 2),
            'days_left': round(days_left, 1),
            'reorder_suggestion': reorder_suggestion
        }
        
    def get_reorder_list(self):
        # 获取需要补货的物品列表
        conn = sqlite3.connect('warehouse_management.db')
        cursor = conn.cursor()
        cursor.execute("SELECT id, quantity, name FROM items WHERE quantity <= ?", (self.reorder_threshold,))
        items = cursor.fetchall()
        conn.close()
        
        reorder_list = []
        for item in items:
            analysis = self.analyze_consumption(item[0])
            if analysis:
                reorder_list.append(analysis)
        
        return reorder_list

# 创建库存预测器实例
inventory_predictor = InventoryPredictor()

# 处理RFID标签数据
def process_rfid_data(data):
    try:
        # 解析数据格式: TAG,标签ID,操作类型(IN/OUT),读写器ID
        parts = data.split(',')
        if len(parts) == 4 and parts[0] == 'TAG':
            tag_id = parts[1]
            action = parts[2]
            reader_id = int(parts[3])
            
            # 记录时间
            timestamp = time.strftime('%Y-%m-%d %H:%M:%S')
            
            # 查询物品信息
            conn = sqlite3.connect('warehouse_management.db')
            cursor = conn.cursor()
            cursor.execute("SELECT id, name, quantity, location FROM items WHERE tag_id = ?", (tag_id,))
            item = cursor.fetchone()
            
            if action == 'IN':
                if item:
                    # 物品已存在，增加数量
                    new_quantity = item[2] + 1
                    cursor.execute(
                        "UPDATE items SET quantity = ?, updated_at = ? WHERE id = ?",
                        (new_quantity, timestamp, item[0])
                    )
                    location = item[3]
                else:
                    # 新物品，需要先在系统中注册
                    # 这里简化处理，实际应用中应该有物品注册流程
                    print(f"未注册的标签: {tag_id}")
                    conn.close()
                    return
                
                # 更新位置计数
                cursor.execute("UPDATE locations SET current_count = current_count + 1 WHERE name = ?", (location,))
                
            elif action == 'OUT':
                if item and item[2] > 0:
                    # 减少库存数量
                    new_quantity = item[2] - 1
                    cursor.execute(
                        "UPDATE items SET quantity = ?, updated_at = ? WHERE id = ?",
                        (new_quantity, timestamp, item[0])
                    )
                    location = item[3]
                    
                    # 更新位置计数
                    cursor.execute("UPDATE locations SET current_count = current_count - 1 WHERE name = ?", (location,))
                else:
                    print(f"出库失败: 物品不存在或库存不足 ({tag_id})")
                    conn.close()
                    return
                
            # 记录出入库日志
            cursor.execute(
                "INSERT INTO inventory_logs (tag_id, item_name, action, quantity, location, reader_id, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?)",
                (tag_id, item[1], action, 1, location, reader_id, timestamp)
            )
            
            conn.commit()
            conn.close()
            
            # 发送确认消息到Arduino
            if ser and ser.is_open:
                response = f"CONFIRM,{tag_id},{action}\n"
                ser.write(response.encode('utf-8'))
                
            print(f"处理完成: {action} {item[1]} ({tag_id})")
            
    except Exception as e:
        print(f"处理RFID数据错误: {e}")

# 数据接收线程
def data_receive_thread():
    simulated_counter = 0
    while True:
        try:
            if ser and ser.is_open:
                if ser.in_waiting > 0:
                    line = ser.readline().decode('utf-8').strip()
                    
                    # 处理RFID标签数据
                    if line.startswith('TAG,'):
                        process_rfid_data(line)
                    elif line.startswith('MODE:'):
                        # 记录模式切换
                        mode = line[5:]
                        print(f"模式切换: {mode}")
                    elif line.startswith('STATUS,'):
                        # 记录状态响应
                        status = line[7:]
                        print(f"系统状态: {status}")
            elif simulation_mode:
                # 模拟模式 - 每5秒生成一次模拟数据
                time.sleep(5)
                simulated_counter += 1
                
                # 每5次迭代切换模式，其余时间处理标签
                if simulated_counter % 5 == 0:
                    # 切换模式
                    mode_index = (simulated_counter // 5 - 1) % len(simulated_mode_switches)
                    current_mode = simulated_mode_switches[mode_index]
                    print(f"[模拟模式] 模式切换为: {current_mode}")
                else:
                    # 处理标签
                    tag_index = (simulated_counter - 1) % len(simulated_tags)
                    tag_data = simulated_tags[tag_index]
                    tag_id = tag_data['tag_id']
                    action = tag_data['action']
                    print(f"[模拟模式] 收到标签: {tag_id}, 动作: {action}")
                    
                    # 构造数据行并处理
                    simulated_line = f"TAG,{tag_id},{action},1"  # 假设reader_id为1
                    process_rfid_data(simulated_line)
            
            time.sleep(0.1)
            
        except Exception as e:
            print(f"数据接收线程错误: {e}")
            time.sleep(1)

# 启动数据接收线程
threading.Thread(target=data_receive_thread, daemon=True).start()

# Web路由
@app.route('/')
def index():
    # 获取仓库概览数据
    conn = sqlite3.connect('warehouse_management.db')
    cursor = conn.cursor()
    
    # 总物品数
    cursor.execute("SELECT COUNT(*) FROM items")
    total_items = cursor.fetchone()[0]
    
    # 总库存数量
    cursor.execute("SELECT SUM(quantity) FROM items")
    total_quantity = cursor.fetchone()[0] or 0
    
    # 最近的出入库记录
    cursor.execute("SELECT * FROM inventory_logs ORDER BY timestamp DESC LIMIT 10")
    recent_logs = cursor.fetchall()
    
    # 需要补货的物品
    reorder_items = inventory_predictor.get_reorder_list()
    
    conn.close()
    
    return render_template('index.html', total_items=total_items, total_quantity=total_quantity, 
                          recent_logs=recent_logs, reorder_items=len(reorder_items))

@app.route('/items')
def item_list():
    # 获取物品列表
    conn = sqlite3.connect('warehouse_management.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM items ORDER BY name")
    items = cursor.fetchall()
    conn.close()
    
    return render_template('items.html', items=items)

@app.route('/locations')
def location_list():
    # 获取位置列表
    conn = sqlite3.connect('warehouse_management.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM locations ORDER BY name")
    locations = cursor.fetchall()
    conn.close()
    
    return render_template('locations.html', locations=locations)

@app.route('/logs')
def log_list():
    # 获取出入库日志
    conn = sqlite3.connect('warehouse_management.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM inventory_logs ORDER BY timestamp DESC")
    logs = cursor.fetchall()
    conn.close()
    
    return render_template('logs.html', logs=logs)

@app.route('/api/add_item', methods=['POST'])
def add_item():
    # 添加新物品
    data = request.json
    
    conn = sqlite3.connect('warehouse_management.db')
    cursor = conn.cursor()
    
    try:
        timestamp = time.strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute(
            "INSERT INTO items (tag_id, name, description, category, quantity, location, unit_price, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (data['tag_id'], data['name'], data['description'], data['category'], data['quantity'], 
             data['location'], data['unit_price'], timestamp, timestamp)
        )
        
        # 更新位置计数
        cursor.execute("UPDATE locations SET current_count = current_count + ? WHERE name = ?", 
                      (data['quantity'], data['location']))
        
        conn.commit()
        conn.close()
        
        return jsonify({'success': True})
    except Exception as e:
        conn.close()
        return jsonify({'success': False, 'message': str(e)})

@app.route('/api/inventory_analysis')
def get_inventory_analysis():
    # 获取库存分析数据
    
    # 获取各类别物品数量
    conn = sqlite3.connect('warehouse_management.db')
    cursor = conn.cursor()
    cursor.execute("SELECT category, COUNT(*) FROM items GROUP BY category")
    category_data = cursor.fetchall()
    
    # 获取最近7天的出入库统计
    seven_days_ago = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute(
        "SELECT DATE(timestamp), action, SUM(quantity) FROM inventory_logs WHERE timestamp >= ? GROUP BY DATE(timestamp), action",
        (seven_days_ago,)
    )
    daily_stats = cursor.fetchall()
    
    conn.close()
    
    # 格式化数据
    categories = [row[0] for row in category_data]
    category_counts = [row[1] for row in category_data]
    
    # 处理每日统计数据
    dates = sorted(list(set([row[0] for row in daily_stats])))
    in_data = []
    out_data = []
    
    for date in dates:
        in_qty = 0
        out_qty = 0
        for row in daily_stats:
            if row[0] == date:
                if row[1] == 'IN':
                    in_qty = row[2]
                else:
                    out_qty = row[2]
        in_data.append(in_qty)
        out_data.append(out_qty)
    
    return jsonify({
        'categories': categories,
        'category_counts': category_counts,
        'dates': dates,
        'in_data': in_data,
        'out_data': out_data
    })

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0')